package elicecn.stock;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import net.sourceforge.pinyin4j.PinyinHelper;

public class AcquireStockPrice {
	private static SimpleDateFormat format = new SimpleDateFormat(
			"yyyyMMddHHmm");
	static final Calendar calendar = Calendar.getInstance();

	static String tabStockCode = "sap.na_code_stock";// 股市代码表
	static String tabStockEconomics = "sap.na_stock_economics";// 大盘指数表
	static String tabStockInfo = "sap.na_stock_info";// 个股行情表
	static String colStockCode = "stock_code";// 股票代码字段

	static final String SHEN_ZHEN = "sz";
	static final String SHANG_HAI = "sh";

	static final String ORACLE = "ORACLE";
	static final String MySQL = "MySQL";
	static final String DATABASE = ORACLE;// ORACLE|MySQL

	static long timeCycle = 5 * 60 * 1000;// 5分钟刷新一次
	// 开始时间
	static int BEGIN_HOUR_AM = 9;
	static int BEGIN_MINUTE_AM = 0;
	// 结束时间
	static int FINISH_HOUR_AM = 11;
	static int FINISH_MINUTE_AM = 30;
	// 开始时间
	static int BEGIN_HOUR_PM = 13;
	static int BEGIN_MINUTE_PM = 0;
	// 结束时间
	static int FINISH_HOUR_PM = 15;
	static int FINISH_MINUTE_PM = 0;

	/**
	 * 请求获取网页信息
	 * 
	 * @param url
	 *            页面地址
	 * @param encoding
	 *            页面编码
	 * @return 页面内容
	 */
	public static String request(String url, String encoding) {
		StringBuffer content = new StringBuffer();
		try {
			URL u = new URL(url);
			InputStream in = new BufferedInputStream(u.openStream());
			InputStreamReader theHTML = new InputStreamReader(in, encoding);
			int c;
			while ((c = theHTML.read()) != -1) {
				content.append((char) c);
			}
		} catch (MalformedURLException e) {
			System.err.println(e);
		} catch (IOException e) {
			System.err.println(e);
		}
		return content.toString();
	}

	/**
	 * 获取行情信息
	 * 
	 * @param queryCode
	 *            查询代码（不是股票代码；深成指代码是s_sz399001；沪综指代码是s_sh000001；
	 *            深市股票000001代码是sz000001 ；沪市股票600000代码是sh600000）
	 * @return 行情信息
	 * @throws IOException
	 */
	public static String acquire(String queryCode) throws IOException {
		String url = "http://hq.sinajs.cn/list=" + queryCode;
		String[] arr = request(url, "gbk").split("\"");
		if (arr.length == 3) {
			return arr[1];
		}
		return null;
	}

	/**
	 * 创建SQL删除语句
	 * 
	 * @param tableName
	 *            表名
	 * @param columnName
	 *            列名
	 * @param value
	 *            值
	 * @return 删除语句
	 */
	public static String createDeleteSql(String tableName, String columnName,
			String value) {
		String sql = "delete from #tableName# where #columnName# = '#value#'";
		sql = sql.replace("#tableName#", tableName);
		sql = sql.replace("#columnName#", columnName);
		sql = sql.replace("#value#", value);
		return sql;
	}

	/**
	 * 创建SQL插入行情语句
	 * 
	 * @param tableName
	 *            表名
	 * @param stockCode
	 *            代码
	 * @param extInfo
	 *            内容 {@link #acquire(String)的返回值}
	 * @return 插入行情语句
	 */
	public static String createInsertSql(String tableName, String stockCode,
			String extInfo) {
		String sql = "";
		String time = format.format(new Date());

		if (extInfo != null && extInfo.length() > 0) {
			extInfo = extInfo.replaceAll(",", "','");
			sql = "insert into #tableName# values('#stockCode#', to_date('"
					+ time + "', 'yyyymmddhh24mi'),'#extInfo#')";
			sql = sql.replace("#tableName#", tableName);
			sql = sql.replace("#stockCode#", stockCode);
			sql = sql.replace("#extInfo#", extInfo);
		}
		return sql;
	}

	/**
	 * 更新股市信息（大盘和股票）
	 * 
	 * @param queryCode
	 *            查询代码
	 * @param stockCode
	 *            股票代码
	 * @param tableName
	 *            表名
	 * @param connection
	 *            数据库连接
	 * @return 是否成功
	 * @throws IOException
	 */
	public static boolean updateStock(String queryCode, String stockCode,
			String tableName, Connection connection) throws IOException {
		String delete = createDeleteSql(tableName, colStockCode, stockCode);
		String insert = createInsertSql(tableName, stockCode,
				acquire(queryCode));
		System.out.println(delete);
		System.out.println(insert);
		try {
			connection.setAutoCommit(false);
			Statement stt = connection.createStatement();
			stt.executeUpdate(delete);
			stt.executeUpdate(insert);
			connection.commit();
			stt.close();
			System.out.println(stockCode);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		return false;
	}

	/**
	 * 获取数据库连接
	 * 
	 * @param driverClass
	 * @param url
	 * @param user
	 * @param password
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection getConnection(String driverClass, String url,
			String user, String password) throws ClassNotFoundException,
			SQLException {
		Class.forName(driverClass);
		return DriverManager.getConnection(url, user, password);
	}

	/**
	 * 获取Oracle连接
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection createOracleConnection()
			throws ClassNotFoundException, SQLException {
		String driverClass = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@10.239.137.103:1521:oraxgx2";
		String user = "sap";
		String password = "oracle";
		return getConnection(driverClass, url, user, password);
	}

	/**
	 * 获取MySQL连接
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection createMySQLConnection()
			throws ClassNotFoundException, SQLException {
		String driverClass = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test";
		String user = "root";
		String password = "security";
		return getConnection(driverClass, url, user, password);
	}

	public static Connection createConnection(String database)
			throws ClassNotFoundException, SQLException {
		if (database.equals(ORACLE)) {
			return createOracleConnection();
		} else if (database.equals(MySQL)) {
			return createMySQLConnection();
		}
		return null;
	}

	/**
	 * 创建SQL插入股市代码
	 * 
	 * @param queryCode
	 * @param stockCode
	 * @param stockArea
	 * @param stockName
	 * @param stockPinyin
	 * @return
	 */
	public static String createStockCodeInsertSql(String queryCode,
			String stockCode, String stockArea, String stockName,
			String stockPinyin) {
		String sql = "insert into " + tabStockCode
				+ "(query_code, stock_code, stock_area, "
				+ "stock_name, stock_pinyin)values"
				+ "('#queryCode#', '#stock_code#', '#stock_area#', "
				+ "'#stock_name#', '#stock_pinyin#')";
		sql = sql.replace("#queryCode#", queryCode);
		sql = sql.replace("#stock_code#", stockCode);
		sql = sql.replace("#stock_area#", stockArea);
		sql = sql.replace("#stock_name#", stockName);
		sql = sql.replace("#stock_pinyin#", stockPinyin);
		return sql;
	}

	/**
	 * 删除股市代码
	 * 
	 * @param queryCode
	 * @return
	 */
	public static String createStockCodeDeleteSql(String queryCode) {
		String sql = "delete from " + tabStockCode
				+ " where query_code='#queryCode#'";
		sql = sql.replace("#queryCode#", queryCode);
		return sql;
	}

	/**
	 * 保存股票代码数据
	 * 
	 * @param queryCode
	 * @param stockCode
	 * @param stockArea
	 * @param connection
	 * @return
	 * @throws IOException
	 * @throws SQLException
	 */
	public static boolean initStock(String queryCode, String stockCode,
			String stockArea, Connection connection) throws IOException,
			SQLException {
		String info = acquire(queryCode);
		if (info != null && info.length() > 0) {
			// 先删除
			String delete = createStockCodeDeleteSql(queryCode);
			Statement stm = connection.createStatement();
			stm.execute(delete);
			stm.close();
			// 再插入
			String stockName = info.split(",")[0];
			String stockPinyin = toPinyinHeader(stockName);
			String sql = createStockCodeInsertSql(queryCode, stockCode,
					stockArea, stockName, stockPinyin);
			Statement stt = connection.createStatement();
			stt.execute(sql);
			stt.close();
			System.out.println(stockCode + stockName + stockPinyin);
			return true;
		}
		return false;
	}

	/**
	 * 转换拼音首字母（多音字取第一个音，非汉字原样输出）
	 * 
	 * @param str
	 * @return
	 */
	public static String toPinyinHeaderSimple(String str) {
		StringBuffer buf = new StringBuffer();
		str = str.toLowerCase();
		for (int i = 0; i < str.length(); i++) {
			char word = str.charAt(i);
			String[] pinyinArray = PinyinHelper.toHanyuPinyinStringArray(word);
			if (pinyinArray != null) {
				buf.append(pinyinArray[0].charAt(0));
			} else if (word >= 'a' && word <= 'z') {
				buf.append(word);
			}
		}
		return buf.toString();
	}

	/**
	 * 转换拼音首字母
	 * 
	 * @param str
	 * @return
	 */
	public static String toPinyinHeader(String str) {
		List<String[]> list = new ArrayList<String[]>();
		str = str.toLowerCase();
		for (int i = 0; i < str.length(); i++) {
			char word = str.charAt(i);
			String[] pinyinArray = PinyinHelper.toHanyuPinyinStringArray(word);
			if (pinyinArray != null) {
				list.add(pinyinArray);
			} else if (word >= 'a' && word <= 'z') {
				String[] words = { String.valueOf(word) };
				list.add(words);
			}
		}
		List<String> result = mergeWords(true, null, list, 0);
		Set<String> rst = new HashSet<String>(result);
		StringBuffer buf = new StringBuffer();

		int size = rst.size();
		String[] strArr = new String[size];
		rst.toArray(strArr);
		for (int i = 0; i < strArr.length; i++) {
			buf.append(strArr[i]);
			if (i + 1 < size) {
				buf.append("/");
			}
		}
		return buf.toString();
	}

	/**
	 * 合并拼音
	 * 
	 * @param flag
	 *            ture = 只合并首字母
	 * @param list
	 * @param list2
	 * @param idx
	 * @return
	 */
	public static List<String> mergeWords(boolean flag, List<String> list,
			List<String[]> list2, int idx) {
		if (idx == list2.size()) {
			return list;
		}
		List<String> result = new ArrayList<String>();
		int size = list != null ? list.size() : 0;
		if (size == 0) {
			String[] strArr = list2.get(idx);
			for (int i = 0; i < strArr.length; i++) {
				result.add(flag ? strArr[i].substring(0, 1) : strArr[i]);
			}
			return mergeWords(flag, result, list2, idx + 1);
		} else {
			for (int i = 0; i < size; i++) {
				String[] strArr = list2.get(idx);
				for (int j = 0; j < strArr.length; j++) {
					String str = flag ? strArr[j].substring(0, 1) : strArr[j];
					String letters = list.get(i) + str;
					result.add(letters);
				}
			}
			return mergeWords(flag, result, list2, idx + 1);
		}
	}

	/**
	 * 非六位数字前面加'0'
	 * 
	 * @param i
	 * @return
	 */
	public static String codePrefix(int i) {
		String code = String.valueOf(i);
		while (code.length() < 6) {
			code = "0" + code;
		}
		return code;
	}

	/**
	 * 初始化股票（生成所有可用股票）
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws IOException
	 */
	public static void init() throws ClassNotFoundException, SQLException,
			IOException {
		// 截止2010-07013
		// 沪A 600000-601999
		// 沪B 900900-900999
		// 深A 000001-000999 001696 001896(主版)
		// 深A 002001-002499(中小版) 300001-300099(创业版)
		// 深B 200001-200999
		Connection cnt = createConnection(DATABASE);
		// 清空数据表
		Statement stt = cnt.createStatement();
		stt.execute("truncate table " + tabStockCode);
		stt.close();
		// 开始加数据
		// 沪A
		for (int i = 600000; i <= 601999; i++) {
			initStock(SHANG_HAI + codePrefix(i), codePrefix(i), SHANG_HAI, cnt);
		}
		// 沪B
		for (int i = 900900; i <= 900999; i++) {
			initStock(SHANG_HAI + codePrefix(i), codePrefix(i), SHANG_HAI, cnt);
		}
		// 深A
		for (int i = 1; i <= 999 || i == 1696 || i == 1896; i++) {
			initStock(SHEN_ZHEN + codePrefix(i), codePrefix(i), SHEN_ZHEN, cnt);
		}
		// 深A中小版
		for (int i = 2001; i <= 2499; i++) {
			initStock(SHEN_ZHEN + codePrefix(i), codePrefix(i), SHEN_ZHEN, cnt);
		}
		// 深A创业版
		for (int i = 300001; i <= 300099; i++) {
			initStock(SHEN_ZHEN + codePrefix(i), codePrefix(i), SHEN_ZHEN, cnt);
		}
		// 深B
		for (int i = 200001; i <= 200999; i++) {
			initStock(SHEN_ZHEN + codePrefix(i), codePrefix(i), SHEN_ZHEN, cnt);
		}
		cnt.close();
	}

	/**
	 * 刷新股票数据
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws IOException
	 */
	public static void refreshStockInfo() throws ClassNotFoundException,
			SQLException, IOException {
		// 查询代码格式：
		// 股票：上海/深圳首字母+股票代码(sh000000)
		// 指数："s_"+上海/深圳首字母+指数代码(s_sh000001)
		Connection cnt = createConnection(DATABASE);
		Statement stt = cnt.createStatement();
		String sql = "select * from " + tabStockCode;
		ResultSet rs = stt.executeQuery(sql);
		while (rs.next()) {
			String queryCode = rs.getString("query_code");
			String stockCode = rs.getString("stock_code");
			updateStock(queryCode, stockCode, tabStockInfo, cnt);
		}
		updateStock("s_sh000001", "000001", tabStockEconomics, cnt);// 沪综指
		updateStock("s_sz399001", "399001", tabStockEconomics, cnt);// 深成指
		rs.close();
		stt.close();
		cnt.close();
	}

	/**
	 * 循环运行
	 * 
	 * @throws InterruptedException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws IOException
	 */
	public static void run() throws InterruptedException,
			ClassNotFoundException, SQLException, IOException {
		while (true) {
			long curTime = System.currentTimeMillis();
			if (curTime >= getRunBeginTimeAM()
					&& curTime <= getRunFinishTimeAM()
					|| curTime >= getRunBeginTimePM()
					&& curTime <= getRunFinishTimePM()) {
				refreshStockInfo();
				long execTime = System.currentTimeMillis() - curTime;
				if (execTime > timeCycle) {// 如果执行时间过5分钟则立即进行新一轮运行
					long sleepTime = timeCycle - execTime;
					if (sleepTime > 0) {
						Thread.sleep(sleepTime);// 如果执行时间少于5分钟，则休眠一段时间
					}
				}
			} else {
				Thread.sleep(timeCycle);
			}
		}
	}

	public static long getRunBeginTimeAM() {
		calendar.set(Calendar.HOUR_OF_DAY, BEGIN_HOUR_AM);
		calendar.set(Calendar.MINUTE, BEGIN_MINUTE_AM);
		return calendar.getTimeInMillis();
	}

	public static long getRunBeginTimePM() {
		calendar.set(Calendar.HOUR_OF_DAY, BEGIN_HOUR_PM);
		calendar.set(Calendar.MINUTE, BEGIN_MINUTE_PM);
		return calendar.getTimeInMillis();
	}

	public static long getRunFinishTimeAM() {
		calendar.set(Calendar.HOUR_OF_DAY, FINISH_HOUR_AM);
		calendar.set(Calendar.MINUTE, FINISH_MINUTE_AM);
		return calendar.getTimeInMillis();
	}

	public static long getRunFinishTimePM() {
		calendar.set(Calendar.HOUR_OF_DAY, FINISH_HOUR_PM);
		calendar.set(Calendar.MINUTE, FINISH_MINUTE_PM);
		return calendar.getTimeInMillis();
	}

	/**
	 * 测试
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws IOException
	 */
	public static void test() throws ClassNotFoundException, SQLException,
			IOException {
		long t1 = System.currentTimeMillis();
		init();
		long t2 = System.currentTimeMillis();
		System.out.println("初始化股票   " + (t2 - t1) + "ms");
		refreshStockInfo();
		long t3 = System.currentTimeMillis();
		System.out.println("刷新股票数据 " + (t3 - t2) + "ms");
	}

	public static void main(String[] args) throws IOException,
			ClassNotFoundException, SQLException, InterruptedException {
		test();
	}
}
